<?php

// +----------------------------------------------------------------------
// | Author: 发仔 <i@fazi.me> 
// +----------------------------------------------------------------------

namespace app\admin\controller;

use think\Db;

class Finance extends Base {

    protected $id = 'car_use_id';
    protected $table = 'car_use';

    protected function search() {

        $P = input('param.');
        $where = ['name' => ['like', "%{$P['name']}%"]];

        //公务只能看公务订单
        if ($this->user['official']) {
            $where['quota'] = ['>', 0];
        }

        return $where;
    }

    public function limit() {

        $this->assign('notPopup', 0);
        return $this->out();
    }

    //修改额度
    public function quota() {

        $id = input('post.id');
        $quota = input('post.quota');

        //act inc:+ dec:-
//        $range = $act == 'inc' ? $quota : $quota*-1;

        $rslt = 0;
        if ($id) {
            $rslt = Db::name('CarUse')->where('car_use_id', $id)->setInc('quota', $quota);
            //历史
            $rslt && $rslt = Db::name('CarUseLog')->exp('datetime', 'NOW()')->insert(['car_use_id' => $id, 'quota' => $quota]);

            if (!$rslt) {
                Db::name('CarUse')->where('car_use_id', $id)->setDec('quota', $quota);
            }
        }

        return $this->res($rslt);
    }

    function log() {

        $id = input('param.car_use_id');
        $list = Db::name('CarUseLog')->where('car_use_id', $id)->whereNull('delete_time')->select();

        $info = Db::name('CarUse')->where('car_use_id', $id)->find();

        $this->assign('list', $list);
        $this->assign('info', $info);
        $this->assign('notPopup', 0);

        return $this->out();
    }

    //充值记录
    public function FundsManagement_list() {

        $P = input('param.');

        if ($P['page']) {
            
        } else {
            $P['page'] = 1;
        }

        if ($P['pagenum']) {
            
        } else {
            $P['pagenum'] = 10;
        }

        $list = Db::name('company_recharge')->page($P['page'], $P['pagenum'])->select();
        $company_return = Db::name('company')->where('')->select();

        if ($list) {
            if (!empty($list)) {
                foreach ($list as $key => $val) {
                    foreach ($company_return as $key2 => $val2) {
                        if ($val['company_id'] == $val2['company_id']) {
                            $list[$key]['company_name'] = $val2['company_name'];
                        }
                    }
                }
            }
        }
        $data = array(
            'code' => 1,
            'msg' => 'ok',
            'time' => time(),
            'data' => $list
        );
        return json($data);
    }

    //通过审批
    public function check_FundsManagement() {

        $P = input('param.');

        $car_use_recharge_return = Db::name('company_recharge')->where('company_recharge_id', $P['company_recharge_id'])->find();

        if ($car_use_recharge_return['state'] == '1' && $car_use_recharge_return['type'] == '2' && $P['notcheck'] == '1') {
            $car_use_recharge_return = Db::name('company_recharge')->where('company_recharge_id', $P['company_recharge_id'])->update(['state' => '4']);

            if ($car_use_recharge_return) {
                return $this->success('已拒绝该充值申请');
            }
        } else if ($car_use_recharge_return['state'] == '1' && $car_use_recharge_return['type'] == '2') {

            $company_return = Db::name('company')->where('company_id', $car_use_recharge_return['company_id'])->find();

            $company_return['unit_quota'] += $car_use_recharge_return['number'];


            $company_return = Db::name('company')->where('company_id', $car_use_recharge_return['company_id'])->update(['unit_quota' => $company_return['unit_quota']]);

            $car_use_recharge_return = Db::name('company_recharge')->where('company_recharge_id', $P['company_recharge_id'])->update(['state' => '3']);

            if ($car_use_recharge_return) {
                return $this->success('已通过');
            } else {
                return $this->error('未知错误');
            }
        } else {
            return $this->error('类型错误或者状态错误');
        }
    }

    //订单汇总表
    public function orderSummary() {
        $P = input('param.');
        $order_Summary = Db::name('company')->alias('a')->join('order b', 'a.company_id=b.company_id', 'left')->
                join('order_fee c', 'b.order_id=c.order_id', 'left')->
                field('a.company_name,b.order_id,c.passenger_money,b.payment_time,b.order_state,a.company_id')->
                where('a.company_name', ['LIKE', "%{$P['company']}%"])->
                group('a.company_id')->paginate(10, false, ['query' => request()->param()])
                ->each(function($order_Summary, $key) {
            $order_Summary['official'] = $this->official($order_Summary['company_id']); //平台公务
            $order_Summary['personal'] = $this->personal($order_Summary['company_id']); //个人出行
            $order_Summary['other'] = $this->other($order_Summary['company_id']); //其他出行
            $order_Summary['Total'] = $order_Summary['official'] + $order_Summary['personal'] + $order_Summary['other']; //总和
            return $order_Summary;
        });
        $this->assign('order_Summary', $order_Summary);
        $this->assign('page', $order_Summary->render());
        return $this->out();
    }

//平台公务计算
    public function official($company_id) {
        $P = input('param.');
        $where = [];
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['b.payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['b.payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['b.payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $official = Db::name('company')->alias('a')->join('order b', 'a.company_id=b.company_id', 'left')->
                        join('order_fee c', 'b.order_id=c.order_id', 'left')->where('b.payment_time', ['EXP', 'IS NOT NULL'])->
                        field('a.company_name,b.order_id,c.passenger_money,b.payment_time,b.order_state,a.company_id')->
                        where($where)->
                        where([
                            'b.order_state' => 11,
                            'b.pay_type' => 3
                        ])->where('a.company_id', $company_id)->sum('c.passenger_money'); //平台公务
        return $official;
    }

    //个人出行计算
    public function personal($company_id) {
        $P = input('param.');
        $where = [];
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['b.payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['b.payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['b.payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $personal = Db::name('company')->alias('a')->join('order b', 'a.company_id=b.company_id', 'left')->
                        join('order_fee c', 'b.order_id=c.order_id', 'left')->where('b.payment_time', ['EXP', 'IS NOT NULL'])->
                        field('a.company_name,b.order_id,c.passenger_money,b.payment_time,b.order_state,a.company_id')->
                        where('b.payment_time', 'between time', [$P['start_time'], $P['end_time']])->
                        where($where)->
                        where([
                            'b.order_state' => 11,
                            'b.pay_type' => 1
                        ])->where('a.company_id', $company_id)->sum('passenger_money'); //个人出行
        return $personal;
    }

    //其他出行计算
    public function other($company_id) {
        $P = input('param.');
        $where = [];
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['b.payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['b.payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['b.payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $other = Db::name('company')->alias('a')->join('order b', 'a.company_id=b.company_id', 'left')->
                        join('order_fee c', 'b.order_id=c.order_id', 'left')->where('b.payment_time', ['EXP', 'IS NOT NULL'])->
                        field('a.company_name,b.order_id,c.passenger_money,b.payment_time,b.order_state,a.company_id')->
                        where('b.payment_time', 'between time', [$P['start_time'], $P['end_time']])->
                        where($where)->
                        where([
                            'b.order_state' => 11,
                            'b.pay_type' => 2
                        ])->where('a.company_id', $company_id)->sum('passenger_money'); //其他出行

        return $other;
    }

    public function outexcel_orderSummary() {
        ini_set('max_execution_time', '100');
        $P = input('param.');
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['b.payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['b.payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['b.payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $order_Summary = Db::name('company')->alias('a')->join('order b', 'a.company_id=b.company_id', 'left')->
                        join('order_fee c', 'b.order_id=c.order_id', 'left')->
                        field('a.company_name,b.order_id,c.passenger_money,b.payment_time,b.order_state,a.company_id')->
                        where('a.company_name', ['LIKE', "%{$P['company']}%"])->
                        group('a.company_id')->select();
        foreach ($order_Summary as $k1 => &$v1) {
            $order_Summary[$k1]['official'] = $this->official($order_Summary[$k1]['company_id']); //公务出行
            $order_Summary[$k1]['bus'] = 0; //中大巴出行
            $order_Summary[$k1]['personal'] = $this->personal($order_Summary[$k1]['company_id']); //个人出行
            $order_Summary[$k1]['other'] = $this->other($order_Summary[$k1]['company_id']); //其他出行
            $order_Summary[$k1]['Total'] = $order_Summary[$k1]['official'] + $order_Summary[$k1]['personal'] + $order_Summary[$k1]['other'];
        }
        foreach ($order_Summary as &$v2) {
            unset($v2['company_id']);
            unset($v2['payment_time']);
            unset($v2['order_id']);
            unset($v2['passenger_money']);
            unset($v2['order_state']);
        }
        if ($order_Summary) {
            echo "<table border='1'>";
            //标题
            $title = array("公司", "平台公务", "中大巴出行", "个人出行", "其他出行", "合计");
            //文件名
            if ($P['start_time'] && !$P['end_time']) {
                $today = date('Y-m-d');
                $filename = $P['start_time'] . '至' . $today . '平台汇总导出报表';
            } elseif (!$P['start_time'] && $P['end_time']) {
                $end_time = date('Y-m-d', strtotime($P['end_time']));
                $filename = $end_time . '之前的平台汇总导出报表';
            } elseif ($P['start_time'] && $P['end_time']) {
                $end_time = date('Y-m-d', strtotime($P['end_time']));
                $filename = $P['start_time'] . '至' . $end_time . '平台汇总导出报表';
            } elseif (!$P['start_time'] && !$P['end_time']) {
                $filename = '平台汇总导出报表';
            }
            header("Content-type:application/octet-stream");
            header("Accept-Ranges:bytes");
            header("Content-type:application/vnd.ms-excel");
            header("Content-Disposition:attachment;filename=" . $filename . ".xls");
            header("Pragma: no-cache");
            header("Expires: 0");
            if (!empty($title)) {
                foreach ($title as $k => $v) {
                    $title[$k] = iconv("UTF-8", "GB2312", "<td>" . $title[$k] . "</td>");
                    echo $title[$k];
                }
                echo "</tr>";
            }
            if (!empty($order_Summary)) {
                foreach ($order_Summary as $key => $val) {
                    foreach ($val as $ck => $cv) {
                        $order_Summary[$key][$ck] = iconv("UTF-8", "GB2312", "<td style=\"vnd.ms-excel.numberformat:@\">" . $order_Summary[$key][$ck] . "</td>");
                        echo $order_Summary[$key][$ck];
                    }
                    echo "</tr>";
                }
            }
        } else {
            $this->error('无数据');
        }
    }

//交通明细表
    public function trafficDetail() {
        $P = input('param.');
        $where=[];
        $where['company_name'] = ['LIKE', "%{$P['company']}%"];
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $P['name'] && $where['a.passenger_name'] = ['LIKE', "%{$P['name']}%"];
        //  halt($where);
        $data = Db::name('order')->alias('a')->join('company b', 'a.company_id=b.company_id', 'left')
                        ->join('order_fee c', 'a.order_id=c.order_id', 'left')
                        ->join('order_gps d', 'a.order_id=d.order_id', 'left')
                        ->join('driver e', 'a.driver_id=e.driver_id', 'left')
                        ->join('car_type f','a.car_type_id=f.car_type_id','left')
                        ->field('a.order_no,a.passenger_name,a.passenger_phone,a.use_explain,d.from_addr,d.to_addr,d.go_time,d.arrival_time,f.type,a.booking_type,f.car_type_name,e.name,a.car_number,a.pay_type,c.meter,a.time,c.discount,c.order_money,c.passenger_money,a.payment_time,b.company_name,a.driver_id,a.order_id,a.car_type_id')
                        ->where('a.payment_time', 'between time', [$P['start_time'], $P['end_time']])
                        ->where($where)->where('a.payment_time', ['EXP', 'IS NOT NULL'])->paginate(10, false, ['query' => request()->param()]);
        $this->assign('data', $data);
        return $this->out();
    }

    //交通明细表导出
    public function trafficDetail_outexcel(){
          $P = input('param.');
          //halt($P);
        $where=[];
        $where['company_name'] = ['LIKE', "%{$P['company']}%"];
        if ($P['start_time'] && !$P['end_time']) {
            $P['start_time'] && $where['payment_time'] = ['>=', $P['start_time']];
        } elseif (!$P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['end_time'] && $where['payment_time'] = ['<=', $P['end_time']];
        } elseif ($P['start_time'] && $P['end_time']) {
            $P['end_time'] .= " 23:59:59";
            $P['start_time'] && $P['end_time'] && $where['payment_time'] = ['BETWEEN', [$P['start_time'], $P['end_time']]];
        }
        $P['name'] && $where['a.passenger_name'] = ['LIKE', "%{$P['name']}%"];
        //  halt($where);
        $data = Db::name('order')->alias('a')->join('company b', 'a.company_id=b.company_id', 'left')
                        ->join('order_fee c', 'a.order_id=c.order_id', 'left')
                        ->join('order_gps d', 'a.order_id=d.order_id', 'left')
                        ->join('driver e', 'a.driver_id=e.driver_id', 'left')
                        ->join('car_type f','a.car_type_id=f.car_type_id','left')
                        ->field('a.order_no,a.passenger_name,a.passenger_phone,a.use_explain,d.from_addr,d.to_addr,d.go_time,d.arrival_time,f.type,a.booking_type,f.car_type_name,e.name,a.car_number,a.pay_type,c.meter,a.time,c.discount,c.order_money,c.passenger_money,a.payment_time,b.company_name,a.driver_id,a.order_id,a.car_type_id')
                        ->where('a.payment_time', 'between time', [$P['start_time'], $P['end_time']])
                        ->where($where)->where('a.payment_time', ['EXP', 'IS NOT NULL'])->select();
        foreach($data as $k1=>$v1){
            $data[$k1]['order_no']=$data[$k1]['order_no'];
            $data[$k1]['passenger_name']=$data[$k1]['passenger_name'];
            $data[$k1]['passenger_phone']=$data[$k1]['passenger_phone'];
            $data[$k1]['use_explain']=$data[$k1]['use_explain'];
            $data[$k1]['from_addr']=$data[$k1]['from_addr'];
            $data[$k1]['to_addr']=$data[$k1]['to_addr'];
              $data[$k1]['go_time']=$data[$k1]['go_time'];
              $data[$k1]['arrival_time']=$data[$k1]['arrival_time'];
              $data[$k1]['type']=in_array($data[$k1]['type'],[4,5])?社会车:公务车;
              if($data[$k1]['booking_type']==1){
                  $data[$k1]['booking_type']='订车（日常）';
              }elseif($data[$k1]['booking_type']==2){
                  $data[$k1]['booking_type']='包车';
              }elseif($data[$k1]['booking_type']==3){
                  $data[$k1]['booking_type']='分时租赁';
              }elseif(empty ($data[$k1]['booking_type'])){
                  $data[$k1]['booking_type']='暂无数据';
              }
               $data[$k1]['car_type_name']=$data[$k1]['car_type_name'];
                $data[$k1]['name']=$data[$k1]['name'];
                $data[$k1]['car_number']=$data[$k1]['car_number'];
                if($data[$k1]['pay_type']==1){
                  $data[$k1]['pay_type']='个人';
              }elseif($data[$k1]['pay_type']==2){
                 $data[$k1]['pay_type']='单位支付';
              }elseif($data[$k1]['pay_type']==3){
                 $data[$k1]['pay_type']='财政支付';
              }elseif($data[$k1]['pay_type']==4){
                 $data[$k1]['pay_type']='自有用车';
              }elseif(empty ($data[$k1]['pay_type'])){
                 $data[$k1]['pay_type']='暂无数据';
              }
              $data[$k1]['meter']=$data[$k1]['meter'];
                        $enddate=$data[$k1]['arrival_time'];$startdate=$data[$k1]['go_time'];
                        $date=floor((strtotime($enddate)-strtotime($startdate))/86400);
                        $hour=floor((strtotime($enddate)-strtotime($startdate))%86400/3600);
                        $minute=floor((strtotime($enddate)-strtotime($startdate))%86400/60);
                        $second=floor((strtotime($enddate)-strtotime($startdate))%86400%60);
                       $data[$k1]['time']=  $date."天".$hour."小时".$minute."分钟".$second."秒";
              $data[$k1]['discount']=$data[$k1]['discount'];
              $data[$k1]['order_money']=$data[$k1]['order_money'];
              $data[$k1]['passenger_money']=$data[$k1]['passenger_money'];
        }
        foreach($data as &$v2){
            unset($v2['company_name']);
            unset($v2['payment_time']);
            unset($v2['driver_id']);
            unset($v2['car_type_id']);
            unset($v2['order_id']);
        }
                  if($data){
            echo "<table border='1'>";
            //标题
            $title = array("订单编号", "乘车人", "联系电话", "用车说明", "出发地点", "到达地点","出发时间","到达时间","用车类别","订车类别","车辆类型","司机姓名","车牌","类型","里程","耗时","折扣(100%)","原价金额","结算金额");
            //文件名
            if ($P['start_time'] && !$P['end_time']) {
                $today = date('Y-m-d');
                $filename = $P['start_time'] . '至' . $today . '交通费明细表';
            } elseif (!$P['start_time'] && $P['end_time']) {
                $end_time = date('Y-m-d', strtotime($P['end_time']));
                $filename = $end_time . '之前的平台汇总导出报表';
            } elseif ($P['start_time'] && $P['end_time']) {
                $end_time = date('Y-m-d', strtotime($P['end_time']));
                $filename = $P['start_time'] . '至' . $end_time . '交通费明细表';
            } elseif (!$P['start_time'] && !$P['end_time']) {
                $filename = '交通费明细表';
            }
            header("Content-type:application/octet-stream");
            header("Accept-Ranges:bytes");
            header("Content-type:application/vnd.ms-excel");
            header("Content-Disposition:attachment;filename=" . $filename . ".xls");
            header("Pragma: no-cache");
            header("Expires: 0");
            if (!empty($title)) {
                foreach ($title as $k => $v) {
                    $title[$k] = iconv("UTF-8", "GB2312", "<td>" . $title[$k] . "</td>");
                    echo $title[$k];
                }
                echo "</tr>";
            }
            if (!empty($data)) {
                foreach ($data as $key => $val) {
                    foreach ($val as $ck => $cv) {
                        $data[$key][$ck] = iconv("UTF-8", "GB2312", "<td style=\"vnd.ms-excel.numberformat:@\">" . $data[$key][$ck] . "</td>");
                        echo $data[$key][$ck];
                    }
                    echo "</tr>";
                }
            }
        } else {
            $this->error('无数据');
        }
    }

}
